<?php
/**
 * DB_Table
 * 
 * 作者:  姚刚 (yaogang@the9sense.com)
 * 创建时间: 2011-03-06 05:48:15
 * 修改记录: 
 * 
 * $Id: Table.php 18 2011-04-17 12:50:07Z coosuu $
 */

class DB_Table {

    /**
     * _dbh
     * DB对象的实例
     *
     * @var DB
     */
    private $_dbh;

    /**
     * _table
     * 当前操作的物理表名
     *
     * @var string
     */
    private $_table;

    /**
     * __construct 
     * 
     * @param  mixed $table 
     * @param  mixed $dsn 
     * @param  mixed $dbuser 
     * @param  mixed $password 
     * @return void
     */
    public function __construct($table, $dsn, $dbuser, $password) {

        $this->_table = $table;
        $this->_dbh = DB::connect($dsn, $dbuser, $password);
    }

    /**
     * getDbh
     * 获取DB对象
     *
     * @return object
     */
    public function getDbh() {

        return $this->_dbh;
    }

    /**
     * 获取一条记录
     *
     * @param string $conditions 
     * @param array $param 
     * @param array columns 列
     * @return array || null
     */
    public function getRow($conditions = null, $params = array(), $columns = array('*')) {

        $columns = implode(',', $columns);
        $sql = "SELECT $columns FROM $this->_table";

        if ($conditions) {
            $sql .= ' WHERE ' . $conditions;
        }

        try {
            $data = $this->_dbh->getRow($sql, $params);
        } catch (PDOException $e) {
            throw new DB_Exception($e->getMessage());
        }

        return $data;
    }

    /**
     * 取得所有的记录
     *
     * @param array $conditions
     * @param array $params
     * @param array $columns
     * @param integer $start
     * @param integer $limit
     * @param string $order
     *   fieldName1 => [ASC|DESC]
     *   fieldName2 => [ASC|DESC]
     * @return array
     */
    public function getAll($conditions = null, $params = array(), $columns = array('*'), $start = 0, $limit = 0, $order = array()) {

        $columns = implode(',', $columns);
        $sql = "SELECT $columns FROM $this->_table ";

        if ($conditions) {
            $sql .= ' WHERE ' . $conditions;
        }

        if ($order && is_array($order)) {
            $orderClause = '';
            foreach ($order as $field => $orderBy) {
                $orderClause .= $field . ' ' . $orderBy . ',';
            }
            $sql .= ' ORDER BY ' . rtrim($orderClause, ',');
        } else if ($order && is_string($order)) {
            $sql .= ' ORDER BY ' . $order;
        }

        if ($limit) {
            $sql .= " LIMIT $start, $limit";
        }

        try {
            $rows = $this->_dbh->getAll($sql, $params);
        } catch (PDOException $e){
            throw new DB_Exception($e->getMessage());
        }
        return $rows;
    }

    /**
     * findBySql
     *
     * @param  string $sql
     * @param  array $params
     * @return array
     */
    public function findBySql($sql, $params = array()) {

        try {
            $sql = $this->_rewriteSql($sql);
            $data = $this->_dbh->getAll($sql, $params);
        } catch (PDOException $e){
            throw new DB_Exception($e->getMessage());
        }
        return $data;
    }

    /**
     * 删除记录
     *
     * @param string $conditions
     * @param array $param
     * @return int affect rows
     */
    public function remove($conditions, $params) {

        $sql = "DELETE FROM $this->_table WHERE $conditions ";
        try {
            echo $sql;
            return $this->_dbh->exec($sql, $params);
        } catch (PDOException $e){
            throw new DB_Exception($e->getMessage());
        }
    }

    /**
     * 插入一条记录 或更新表记录
     *
     * @param array $data
     * @param string $conditions
     * @param array $param
     * @return bool || int
     */
    public function save($data, $conditions = NULL, $params = NULL) {

        $tempParams = array();
        $set = array();
        foreach ($data as $k => $v) {
            array_push($set, $k . '= ?');
            array_push($tempParams, $v);
        }

        if ($conditions) {
            // 更新
            $sql = "UPDATE $this->_table SET " . join(',',$set) . " WHERE $conditions";
            $params = array_merge($tempParams,$params);
        } else {
            // 插入
            $sql = "INSERT INTO  $this->_table SET ". join(',', $set);
            $params = $tempParams;
        }

        try{
            return $this->_dbh->exec($sql, $params);
        } catch (PDOException $e){
            throw new DB_Exception($e->getMessage());
        }
    }

    /**
     * replace
     * 根据主键替换或保存
     *
     * @param array $data
     * @return mixed
     */
    public function replace($data) {

        $tempParams = array();
        $set = array();
        foreach ($data as $k => $v) {
            array_push($set, $k . '= ?');
            array_push($tempParams, $v);
        }

        $sql = "REPLACE INTO $this->_table SET ". join(',', $set);

        try{
            return $this->_dbh->exec($sql, $tempParams);
        } catch (PDOException $e){
            throw new DB_Exception($e->getMessage());
        }
    }

    /**
     * getLastInsertId 
     * 
     * @return void
     */
    public function getLastInsertId() {

        try{
            return $this->_dbh->getLastInsertId();
        } catch (PDOException $e){
            throw new DB_Exception($e->getMessage());
        }
    }

    /**
     * 一次插入多条记录
     *
     * @param  $data
     */
    public function multiInsert($data) {

        $count = count($data);
        $getKeys = array_keys($data[0]);
        $countKeys = count($getKeys);
        $columns = implode(',', $getKeys);

        // 构造问号表达式
        $tmpArr = array();
        for($i=0;$i< $countKeys;$i++) {
            $tmpArr[] = '?';
        }
        $tmpStr = implode(',', $tmpArr);
        $tmpStr = '(' . $tmpStr. ')';
        $tmpArr2 = array();
        $mergeArr = array();
        for ($i=0;$i < $count; $i++){
            $tmpArr2[] = $tmpStr;
            $mergeArr = array_merge($mergeArr, array_values($data[$i]));
        }

        $tmpStr2 = implode(',', $tmpArr2);
        $conditions = "INSERT INTO $this->_table ($columns) VALUES $tmpStr2";
        try {
            return  $this->_dbh->exec($conditions,$mergeArr);
        } catch (PDOException  $e) {
            throw new DB_Exception($e->getMessage());
        }
    }

    /**
     * count
     * 计算行数
     *
     * @param  string $conditions
     * @param  array $params
     * @return integer
     */
    public function count($conditions = NULL, $params = array()) {

        $sql = 'SELECT COUNT(*) FROM ' . $this->_table ;
        try {
            if ($conditions) {
                $sql .= ' WHERE ' . $conditions;
            }
            return  $this->_dbh->getOne($sql, $params);

        } catch (PDOException  $e) {
            throw new DB_Exception($e->getMessage());

        }
    }

    /**
     * exec
     * 执行sql语句
     *
     * @param  string $sql
     * @param  string $params
     * @return void
     */
    public function exec($sql, $params = array()) {

        try {
            $sql = $this->_rewriteSql($sql);
            $result = $this->_dbh->exec($sql, $params);
        } catch (PDOException $e) {
            throw new DB_Exception($e);
        }

        return $result;
    }

    /**
     * incr 
     * 
     * @param  mixed $field 
     * @param  mixed $conditions 
     * @param  array $params 
     * @param  integer $unit 
     * @return void
     */
    public function incr($field, $conditions = null, $params = array(),  $unit = 1) {

        $sql = 'UPDATE ' . $this->_table . " SET `$field` = `$field` + $unit";
        if ($conditions) {
            $sql .= ' WHERE ' . $conditions;
        }
        try {
            $result = $this->_dbh->exec($sql, $params);
        } catch (PDOException $e) {
            throw new DB_Exception($e->getMessage, $e->getCode());
        }
        return $result;
    }

    /**
     * decr 
     * 
     * @param  mixed $field 
     * @param  mixed $conditions 
     * @param  array $params 
     * @param  integer $unit 
     * @return void
     */
    public function decr($field , $conditions = null, $params = array(), $unit = 1) {

        $sql = 'UPDATE ' . $this->_table . " SET $field = IF($field > $unit,  $field - $unit, 0)";
        if ($conditions) {
            $sql .= ' WHERE ' . $conditions;
        }

        try {
            $result = $this->_dbh->exec($sql, $params);
        } catch (PDOException $e) {
            throw new DB_Exception($e->getMessage);
        }
        return $result;
    }

    /**
     * truncate 
     * 
     * @return void
     */
    public function truncate() {

        $sql = "TRUNCATE  $this->_table  ";
        $params = array();
        try {
            return $this->_dbh->exec($sql, $params);
        } catch (PDOException $e) {
            throw new DB_Exception($e->getMessage());
        }
    }

    /**
     * beginTransaction 
     * 
     * @return void
     */
    public function beginTransaction() {

        return $this->_dbh->begin();
    }

    /**
     * commitTransaction 
     * 
     * @return void
     */
    public function commitTransaction() {

        return $this->_dbh->commit();
    }

    /**
     * rollBackTransaction 
     * 
     * @return void
     */
    public function rollBackTransaction() {

        return $this->_dbh->rollBack();
    }

    /**
     * _rewriteSql 
     * 
     * @param  mixed $sql 
     * @return void
     */
    protected function _rewriteSql($sql) {

        $pattern = '/((?:select.*?from|insert into|delete from|update|replace into|truncate table|describe|alter table)\s+)`?(\w+)`?/i';
        return preg_replace($pattern, '\1' . $this->_table, $sql);
    }

}
